package com.iris.live.services.data.repositories;

import com.iris.live.services.data.models.CrmHistoryModel;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.sql.Date;
import java.util.List;

/**
 * Created by lusong on 2016/11/16.
 */
public interface CrmHistoryRepository extends JpaRepository<CrmHistoryModel, Integer>, JpaSpecificationExecutor<CrmHistoryModel> {

    @Query("select a from CrmHistoryModel a where a.permit =?1 and a.crm = ?2 and a.stage =?3 and a.custId =?4 and a.historyState = '1' order by a.updateTime")
    public List<CrmHistoryModel> querySurveyHistory(String permit, String username, String stage, int custId);


    @Query("select a from CrmHistoryModel a where a.permit =?1 and a.stage =?2 and a.custId =?3 and a.connectDate = ?4 and a.serialNumber = ?5")
    CrmHistoryModel querySurveyHistoryByPermitAndCustIdAndStageAndCrm(String permit, String stage, int custId, Date connectDate, int serialNumber);


    @Query("select a from CrmHistoryModel a where a.permit =?1 and a.stage =?2 and a.custId =?3 and a.serialNumber = ?4")
    List<CrmHistoryModel> querySurveyHistoryByPermitAndCustIdAndStageAndCrm(String permit, String stage, int custId, int serialNumber);

    @Query("select a from CrmHistoryModel a where a.permit =?1 and a.stage =?2 and a.custId =?3 and a.connectDate = ?4 and a.serialNumber is null")
    CrmHistoryModel querySurveyHistoryByPermitAndCustIdAndStageAndCrm(String permit, String stage, int custId, Date connectDate);

    @Query("select a from CrmHistoryModel a where a.permit =?1 and a.stage =?2 and a.custId =?3 and a.serialNumber is null")
    List<CrmHistoryModel> querySurveyHistoryByPermitAndCustIdAndStageAndCrm(String permit, String stage, int custId);

    @Query("select count(a.id) from CrmHistoryModel  a where a.historyState = '1' and a.permit = ?1 and a.updateAccount = ?2 and a.stage = ?3 and a.connectDate >= ?4 and a.connectDate <= ?5")
    int queryVisitNumByStage(String permit, String username, String stage, Date startDate, Date endDate);

    @Query("select count(a.id) from CrmHistoryModel  a where a.historyState = '1' and a.permit = ?1 and a.updateAccount = ?2 and a.connectDate >= ?3 and a.connectDate <= ?4")
    int queryVisitNum(String permit, String username, Date startDate, Date endDate);

    @Query("select a from CrmHistoryModel a where a.stage = ?1 and a.custId = ?2")
    List<CrmHistoryModel> queryModelByStageAndCustId(String stage, int custId);

    @Query("select a from CrmHistoryModel a where a.stage = ?1 and a.custId = ?2 and a.serialNumber = ?3")
    List<CrmHistoryModel> queryModelByStageAndCustIdAndSerialNumber(String stage, int custId, int serialNumber);
}
